Imports System.IO
Imports System
Imports System.Collections
Imports System.Runtime.Serialization.Formatters.Soap
Imports System.Runtime.Serialization
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.Data.SqlClient
Imports C3I.CodegenTools

Module basScrap

    Sub buildit()
        Dim conn As New SqlConnection(), FieldDefs As String, nF As Integer, strSQLField As String
        Dim strFuncDef As String, strTable As String, strInsert As String, strUpdate As String, strGetData As String
        Dim strClassName As String, strPrimary As String, bPrimary As Boolean, strTSQL As String, strSQLType As String = ""
        Dim typOrderDateType As SqlDbType, strTSQLFields As String, strTSQLFields2 As String, strSQLPrimary As String, strSQLPrimaryDT As String
        Dim strInsertTSQL As String, strUpdateTSQL As String, strNameSpace As String, strHTML As String, strFieldName As String
        Dim nTextWidth As Integer, strCTLCode As String

        conn.ConnectionString = "Data Source=localhost;Initial Catalog=Corp;Persist Security Info=True;User ID=sa;Password=`1QW~!qw"

        conn.Open()

        Dim reader As SqlDataReader, strField As String
        Dim previousConnectionState As ConnectionState = conn.State

        strClassName = "Store"
        strTable = "tblStoreMaster"
        strNameSpace = "ITAMS.BOL"

        Dim cmd As New SqlCommand("SELECT * FROM " & strTable, conn)
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo)

            'Or _ 
            'CommandBehavior.CloseConnection()
            Dim tbl As DataTable = reader.GetSchemaTable()
            FieldDefs = ""

            ' If you Bind that datatable you Datagrid or any other then you can see
            '** ColumnName, ColumnOrdinal, ColumnSize, NumericPrecision, NumericScale,
            'IsUnique, IsKey, BaseServerName, BaseCatalogName, ** BaseColumnName,
            'BaseSchemaName, BaseTableName, AllowDBNull, ProviderType, IsAliased,
            'IsExpression, IsIdentity,
            '** IsAutoIncrement, IsRowVersion, IsHidden, IsLong, IsReadOnly ***/
            'tbl.Rows(0)("ProviderType")

            strPrimary = "NoPrimary"

            'get the name of primary key
            For nF = 0 To reader.FieldCount - 1

                If tbl.Rows(nF)("IsKey") Then
                    strPrimary = tbl.Rows(nF)("ColumnName")
                    strGetData = "Public Shared Function GetData(ByVal " & strPrimary & " As " & reader.GetFieldType(nF).Name & ") As " & strClassName & vbCrLf
                    Exit For
                End If
            Next

            'build the TSQL Update stored procedure logic
            strUpdateTSQL = "Update " & strTable & " SET "

            strGetData = strGetData & "            Dim db As DBAccess = New DBAccess" & vbCrLf
            strGetData = strGetData & "db.Parameters.Add(New SqlParameter(""@sUserName"", " & strPrimary & "))" & vbCrLf
            strGetData = strGetData & "Dim dr As SqlDataReader = CType(db.ExecuteReader(""SomeStoreProc""), SqlDataReader)" & vbCrLf
            strGetData = strGetData & "If dr.HasRows Then" & vbCrLf
            strGetData = strGetData & "Dim c As " & strClassName & " = New " & strClassName & vbCrLf
            strGetData = strGetData & "While dr.Read" & vbCrLf

            strUpdate = "    Public Shared Function Update(ByVal c As " & strClassName & ") As Integer" & vbCrLf
            strUpdate = strUpdate & "Dim db As DBAccess = New DBAccess" & vbCrLf


            strInsert = "Public Shared Function Insert(ByVal c As " & strClassName & ") As Integer" & vbCrLf
            strInsert = strInsert & "Dim db As DBAccess = New DBAccess" & vbCrLf

            For Each dr As DataRow In tbl.Rows
                'Debug.WriteLine(tbl.Rows(nF)("IsKey").ToString & " " & tbl.Rows(nF)("IsIdentity").ToString & tbl.Rows(nF)("BaseColumnName"))
                Debug.WriteLine(dr("IsKey").ToString & " " & dr("IsIdentity").ToString & dr("BaseColumnName"))
            Next


            For nF = 0 To reader.FieldCount - 1

                strFieldName = reader.GetName(nF)
                strField = reader.GetFieldType(nF).Name.Substring(0, 3).ToLower & strFieldName
                strSQLField = reader.GetFieldType(nF).Name.Substring(0, 1).ToLower & strFieldName

                'determine if current field is primary key
                bPrimary = tbl.Rows(nF)("IsKey")

                'get the field data type 
                typOrderDateType = CType(CType(tbl.Rows(nF)("ProviderType"), Integer), SqlDbType)

                If typOrderDateType = System.Data.SqlDbType.NVarChar Or typOrderDateType = System.Data.SqlDbType.Char _
                    Or typOrderDateType = System.Data.SqlDbType.NChar Then
                    nTextWidth = tbl.Rows(nF)("ColumnSize")
                Else
                    nTextWidth = 5
                End If

                'ColumnSize()

                'Width="14px"
                strHTML = strHTML & "<asp:Label ID=""lbl" & strFieldName & """ runat=""server"" Text=""" & strFieldName & """></asp:Label>" & vbCrLf
                strHTML = strHTML & "<asp:TextBox ID=""txt" & strFieldName & """ runat=""server"" MaxLength=""" & nTextWidth.ToString & """ Width=""" & nTextWidth * 9 & """></asp:TextBox>" & vbCrLf
                strHTML = strHTML & "<br/>" & vbCrLf

                '<asp:Label ID=""Label1"" runat=""server"" Text=""Label""></asp:Label>
                '<asp:TextBox ID=""TextBox2"" runat=""server"" MaxLength=""10""></asp:TextBox>
                '<br />
                strCTLCode = strCTLCode & "txt" & strFieldName & ".Text = obj" & strClassName & "." & strFieldName & vbCrLf


                FieldDefs = FieldDefs & "        Private " & strField & " AS " & reader.GetFieldType(nF).Name & vbCrLf

                strFuncDef = strFuncDef & "        Public Property " & strFieldName & "() As " & reader.GetFieldType(nF).Name & vbCrLf
                strFuncDef = strFuncDef & "            Get" & vbCrLf
                strFuncDef = strFuncDef & "                Return " & strField & vbCrLf
                strFuncDef = strFuncDef & "            End Get" & vbCrLf & vbCrLf
                strFuncDef = strFuncDef & "            Set(ByVal value As " & reader.GetFieldType(nF).Name & ")" & vbCrLf
                strFuncDef = strFuncDef & "                " & strField & " = value" & vbCrLf

                strFuncDef = strFuncDef & "            End Set" & vbCrLf
                strFuncDef = strFuncDef & "        End Property" & vbCrLf & vbCrLf

                'if key then build return string
                If bPrimary Then
                    'strInsert = strInsert & "db.Parameters.Add(New SqlParameter(""@" & strSQLField & """,c." & reader.GetName(nF) & "))" & vbCrLf
                    strInsert = strInsert & "Dim objParam As SqlParameter = New SqlParameter(""@" & strSQLField & """)" & vbCrLf
                    'Dim objParam As SqlParameter = New SqlParameter("@iCompanyID", 0)
                    strInsert = strInsert & "objParam.Direction = ParameterDirection.Output" & vbCrLf
                Else
                    If Not tbl.Rows(nF)("IsAutoIncrement") Then
                        strInsert = strInsert & "db.Parameters.Add(New SqlParameter(""@" & strSQLField & """,c." & reader.GetName(nF) & "))" & vbCrLf
                    End If
                End If


                'if the field is not primary key then build non-key related strings
                If Not bPrimary Then
                    strTSQL = strTSQL & "@" & strSQLField & " " & typOrderDateType.ToString

                    strTSQLFields2 = strTSQLFields2 & "@" & strSQLField & "," & vbCrLf
                    strTSQLFields = strTSQLFields & "[" & reader.GetName(nF) & "]," & vbCrLf

                    strUpdateTSQL = strUpdateTSQL & reader.GetName(nF) & "=" & "@" & strSQLField & "," & vbCrLf

                    'if field is of a data type of specified size (such as character string) then add dimensions
                    If typOrderDateType = System.Data.SqlDbType.NVarChar Or typOrderDateType = System.Data.SqlDbType.Char _
                        Or typOrderDateType = System.Data.SqlDbType.NChar Then

                        strTSQL = strTSQL & "(" & tbl.Rows(nF)("Columnsize").ToString & ")"
                    End If
                    strTSQL = strTSQL & "," & vbCrLf
                Else
                    'store the primary key field name and data type
                    strSQLPrimary = strSQLField
                    strSQLPrimaryDT = typOrderDateType.ToString

                End If

                strGetData = strGetData & "c." & reader.GetName(nF) & " = dr.Get" & reader.GetFieldType(nF).Name & "(dr.GetOrdinal(""" & reader.GetName(nF) & """))" & vbCrLf

                strUpdate = strUpdate & "db.Parameters.Add(New SqlParameter(""@" & strSQLField & """," & "c." & reader.GetName(nF) & "))" & vbCrLf
            Next

            '            db.Parameters.Add(objParam)
            strInsert = strInsert & "db.Parameters.Add(objParam)" & vbCrLf
            strInsert = strInsert & "Dim retval As Integer = db.ExecuteNonQuery(""" & strClassName & "_Insert"")" & vbCrLf
            strInsert = strInsert & "If retval = 1 Then" & vbCrLf
            strInsert = strInsert & "Return Integer.Parse(objParam.Value.ToString)" & vbCrLf
            strInsert = strInsert & "Else" & vbCrLf
            strInsert = strInsert & "Return -1" & vbCrLf
            strInsert = strInsert & "End If" & vbCrLf


            strInsert = strInsert & "End Function" & vbCrLf

            strUpdate = strUpdate & "Dim retval As Integer = db.ExecuteNonQuery(""" & strClassName & "_Update"")" & vbCrLf
            strUpdate = strUpdate & "Return retval" & vbCrLf
            strUpdate = strUpdate & "End Sub" & vbCrLf

            strTSQLFields = strTSQLFields.Substring(0, strTSQLFields.Length - 3) & vbCrLf
            strTSQLFields2 = strTSQLFields2.Substring(0, strTSQLFields2.Length - 3) & vbCrLf

            strGetData = strGetData & "End While" & vbCrLf
            strGetData = strGetData & "            dr.Close()" & vbCrLf
            strGetData = strGetData & "Return c" & vbCrLf
            strGetData = strGetData & "Else" & vbCrLf
            strGetData = strGetData & "dr.Close()" & vbCrLf
            strGetData = strGetData & "Return Nothing" & vbCrLf
            strGetData = strGetData & "End If" & vbCrLf
            strGetData = strGetData & "End Function" & vbCrLf

            'build the TSQL Update stored procedure logic
            strUpdateTSQL = strUpdateTSQL.Substring(0, strUpdateTSQL.Length - 3)
            strUpdateTSQL = " AS " & vbCrLf & strUpdateTSQL
            strUpdateTSQL = strTSQL.Substring(0, strTSQL.Length - 3) & vbCrLf & strUpdateTSQL
            strUpdateTSQL = "@" & strSQLPrimary & " AS " & strSQLPrimaryDT & "," & vbCrLf & strUpdateTSQL
            strUpdateTSQL = "CREATE PROCEDURE " & strClassName & "_Update" & vbCrLf & strUpdateTSQL

            strUpdateTSQL = strUpdateTSQL & " WHERE " & strSQLPrimary.Substring(1) & " = @" & strSQLPrimary

            'build the TSQL Insert stored procedure logic
            strInsertTSQL = "CREATE PROCEDURE " & strClassName & "_Insert" & vbCrLf
            strInsertTSQL = strInsertTSQL & strTSQL & "@" & strSQLPrimary & " " & strSQLPrimaryDT & " " & " OUTPUT " & vbCrLf & " AS "
            strInsertTSQL = strInsertTSQL & vbCrLf & " INSERT [dbo].[" & strTable & "]" & vbCrLf
            strInsertTSQL = strInsertTSQL & "(" & vbCrLf & strTSQLFields & ")" & vbCrLf & "VALUES" & vbCrLf & "(" & vbCrLf & strTSQLFields2
            strInsertTSQL = strInsertTSQL & ")" & vbCrLf & " SELECT @" & strSQLPrimary & "=SCOPE_IDENTITY()" & vbCrLf


            My.Computer.FileSystem.WriteAllText("C:\Test.txt", FieldDefs & vbCrLf, True)
            My.Computer.FileSystem.WriteAllText("C:\Test.txt", strFuncDef & vbCrLf, True)

            My.Computer.FileSystem.WriteAllText("C:\Test.txt", strInsert & vbCrLf, True)

            My.Computer.FileSystem.WriteAllText("C:\Test.txt", strGetData & vbCrLf, True)

            My.Computer.FileSystem.WriteAllText("C:\Test.txt", strUpdate & vbCrLf, True)


            My.Computer.FileSystem.WriteAllText("C:\TSQL.txt", strInsertTSQL, True)

            My.Computer.FileSystem.WriteAllText("C:\TSQL.txt", strUpdateTSQL, True)

            My.Computer.FileSystem.WriteAllText("C:\html.txt", strHTML, True)
            My.Computer.FileSystem.WriteAllText("C:\ctl1.txt", strCTLCode, True)


            'My.Computer.FileSystem.WriteAllText("C:\Test2.txt", strTSQL & "@" & strSQLPrimary & " " _
            '                                & strSQLPrimaryDT & " OUTPUT" & vbCrLf & " AS " & vbCrLf & "INSERT [dbo].[JobsDb_Companies]" & vbCrLf & _
            '                                "(" & vbCrLf & strTSQLFields & ")" & vbCrLf & "VALUES" & vbCrLf & "(" & vbCrLf & strTSQLFields2 _
            '                                 & ")" & vbCrLf & " SELECT @" & strSQLPrimary & "=SCOPE_IDENTITY()", True)



            'Debug.Write()


            ' gscheme()
        Finally
            If previousConnectionState = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try

        'gscheme()

    End Sub


    Sub gscheme()

        Dim strConn, strSQL As String, i As Integer
        strConn = "Data Source=localhost;Initial Catalog=Corp;Persist Security Info=True;User ID=sa;Password=`1QW~!qw"

        Dim cn As New SqlConnection(strConn)
        cn.Open()
        strSQL = "SELECT * FROM tblComputers"
        Dim cmd As New SqlCommand(strSQL, cn)
        Dim rdr As SqlDataReader

        rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly)

        Dim tbl As DataTable = rdr.GetSchemaTable()

        Dim intOrderDateType As Integer
        intOrderDateType = CType(tbl.Rows(0)("ProviderType"), Integer)
        Dim typOrderDateType As SqlDbType
        typOrderDateType = CType(intOrderDateType, SqlDbType)


        Console.WriteLine("ProviderType = " & intOrderDateType)
        Console.WriteLine("SqlDbType = " & typOrderDateType.ToString())


        For i = 0 To rdr.FieldCount - 1
            intOrderDateType = CType(tbl.Rows(i)("ProviderType"), Integer)
            typOrderDateType = CType(intOrderDateType, SqlDbType)

            Debug.WriteLine(rdr.GetName(i) & " " & Replace(rdr.GetFieldType(i).ToString, "System.", "") & " " & typOrderDateType.ToString())

        Next i

        rdr.Close()
        cn.Close()



    End Sub


    Sub xsdf(ByVal a As String, ByVal typ As Type)

    End Sub

    Function PopulateTableOrig(ByVal conn As SqlConnection, ByVal strTable As String) As TableLayout
        Dim FieldDefs As String, nF As Integer, strSQLField As String


        Dim strPrimary As String, bPrimary As Boolean, strSQLType As String = ""
        Dim typOrderDateType As SqlDbType, nC As Integer
        Dim strFieldName As String, newcol As Column
        'Dim systemType As System.run

        Dim nTextWidth As Integer

        Dim tableLayout As New TableLayout
        Dim columndictionary As Dictionary(Of String, String)


        Dim reader As SqlDataReader, strField As String
        Dim previousConnectionState As ConnectionState = conn.State

        'Dim strNameSpace As String, strClassName As String
        'strClassName = "Store"
        'strNameSpace = "ITAMS.BOL"

        tableLayout.Name = strTable

        Dim cmd As New SqlCommand("SELECT * FROM " & strTable, conn)
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo)

            'Or _ 
            'CommandBehavior.CloseConnection()
            Dim tbl As DataTable = reader.GetSchemaTable()
            FieldDefs = ""

            ' If you Bind that datatable you Datagrid or any other then you can see
            '** ColumnName, ColumnOrdinal, ColumnSize, NumericPrecision, NumericScale,
            'IsUnique, IsKey, BaseServerName, BaseCatalogName, ** BaseColumnName,
            'BaseSchemaName, BaseTableName, AllowDBNull, ProviderType, IsAliased,
            'IsExpression, IsIdentity,
            '** IsAutoIncrement, IsRowVersion, IsHidden, IsLong, IsReadOnly ***/
            'tbl.Rows(0)("ProviderType")

            strPrimary = "NoPrimary"

            'get the name of primary key
            For nF = 0 To reader.FieldCount - 1

                If tbl.Rows(nF)("IsKey") Then
                    strPrimary = tbl.Rows(nF)("ColumnName")
                    Exit For
                End If
            Next
            'strPrimary


            For Each dr As DataRow In tbl.Rows
                'Debug.WriteLine(tbl.Rows(nF)("IsKey").ToString & " " & tbl.Rows(nF)("IsIdentity").ToString & tbl.Rows(nF)("BaseColumnName"))
                Debug.WriteLine(dr("IsKey").ToString & " " & dr("IsIdentity").ToString & dr("BaseColumnName"))
            Next

            For nC = 0 To tbl.Columns.Count - 1
                Try
                    Debug.WriteLine(nC & " " & tbl.Columns(nC).ColumnName & " : " & tbl.Rows(0)(nC))
                Catch x As System.Exception
                    Debug.WriteLine("Not happy: " & tbl.Columns(nC).ColumnName)
                End Try
            Next

            For nF = 0 To reader.FieldCount - 1

                columndictionary = New Dictionary(Of String, String)

                strFieldName = reader.GetName(nF)
                strField = reader.GetFieldType(nF).Name.Substring(0, 3).ToLower & strFieldName  'reader.GetFieldType(nF).Name
                strSQLField = reader.GetFieldType(nF).Name.Substring(0, 1).ToLower & strFieldName


                'reader.GetFieldType(nF).Name 
                'If Not tbl.Rows(nF)("IsAutoIncrement") Then
                'determine if current field is primary key
                'tbl.Rows(nF)("Columnsize").ToString 
                'if field is of a data type of specified size (such as character string) then add dimensions
                'If typOrderDateType = System.Data.SqlDbType.NVarChar Or typOrderDateType = System.Data.SqlDbType.Char _
                '    Or typOrderDateType = System.Data.SqlDbType.NChar Then
                'strSQLPrimaryDT = typOrderDateType.ToString

                bPrimary = tbl.Rows(nF)("IsKey")

                'get all columns
                For nC = 0 To tbl.Columns.Count - 1
                    Try
                        Debug.WriteLine(nC & " " & tbl.Columns(nC).ColumnName & " : " & tbl.Rows(0)(nC))

                        columndictionary.Add(tbl.Columns(nC).ColumnName, tbl.Rows(nF)(nC))

                    Catch x As System.Exception
                        Debug.WriteLine("Not happy: " & tbl.Columns(nC).ColumnName)
                    End Try
                Next

                'add custom values to list
                columndictionary.Add("IgnoreColumn", "No")

                'get the SQL field data type 
                typOrderDateType = CType(CType(tbl.Rows(nF)("ProviderType"), Integer), SqlDbType)


                If typOrderDateType = System.Data.SqlDbType.NVarChar Or typOrderDateType = System.Data.SqlDbType.Char _
                    Or typOrderDateType = System.Data.SqlDbType.NChar Then
                    nTextWidth = tbl.Rows(nF)("ColumnSize")
                    columndictionary.Add("Dimensions", "True")
                Else
                    nTextWidth = 5
                    columndictionary.Add("Dimensions", "False")
                End If
                'display text width is approx width in pixels for HTML pages
                columndictionary.Add("DisplayTextWidth", nTextWidth.ToString)

                '.NET data type
                columndictionary.Add("NETDataType", reader.GetFieldType(nF).Name)

                'Add SQL data type
                columndictionary.Add("SQLDataType", typOrderDateType.ToString)
                newcol = New Column
                newcol.Values = columndictionary
                newcol.Name = strFieldName
                newcol.IsKey = bPrimary

                tableLayout.Columns.Add(newcol.Name, newcol)
            Next

        Finally
            If previousConnectionState = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try

        reader.Close()

        Return tableLayout

    End Function

    Sub PopulateDatabaseObjectorig()
        Dim conn As New SqlConnection(), conn2 As New SqlConnection(), newTableLayout As TableLayout, newDatabase As DatabaseStructure, dbServer As New DatabaseServer
        Dim reader As SqlDataReader, dcmd As SqlCommand

        dbServer.Name = "Odin"
        dbServer.ConnectionString = "Data Source=localhost;Initial Catalog=Corp;Persist Security Info=True;User ID=sa;Password=`1QW~!qw"

        'create a database 
        newDatabase = New DatabaseStructure
        newDatabase.Name = "Corp"
        newDatabase.ConnectionString = "Data Source=localhost;Initial Catalog=Corp;Persist Security Info=True;User ID=sa;Password=`1QW~!qw"

        conn.ConnectionString = newDatabase.ConnectionString
        conn.Open()

        conn2.ConnectionString = newDatabase.ConnectionString
        conn2.Open()

        dcmd = New SqlCommand("select table_name from INFORMATION_SCHEMA.TABLES where table_type='BASE TABLE'", conn2)

        reader = dcmd.ExecuteReader

        Do While reader.Read
            newTableLayout = PopulateTable(reader("TABLE_NAME"), newTableLayout) '"tblStoreMaster") 'conn, 
            newDatabase.TableLayouts.Add(reader("TABLE_NAME").ToString, newTableLayout)
        Loop

        dbServer.Databases.Add(newDatabase.Name, newDatabase)


        CreateDBLayoutTreeView(frmMainApp.tvwDatabaseLO, dbServer)

        conn.Close()
        conn2.Close()

        'strTable = "tblStoreMaster"
        pageObjects(1) = dbServer

    End Sub

    Sub SaveSerializedFile()
        Dim layouttemplate As New LayoutTemplate
        Dim section As New SectionTemplate 'detail As New DetailTemplate,
        Dim dt As New DataType

        'dt.Type = "All"
        'detail.DataTypes.Add(dt)

        'detail.Description = "main detail"
        'section.Details.Add(detail)

        section.Description = "Properties section"
        'section.DataTypes.Add("det" & dt.Type & section.Description, dt)
        section.DataTypes.Add(dt)

        layouttemplate.Sections.Add(section)

        section = New SectionTemplate
        'section.Details.Add(detail)
        section.Description = "Constants section"
        section.DataTypes.Add(dt)


        layouttemplate.Sections.Add(section)

        'insert section
        'delete section
        'update section

        layouttemplate.LayoutName = "Generate BO "
        CreateTreeView(frmMainApp.TreeView1, layouttemplate)


    End Sub

    Sub PopulateDatabaseObjectMake()
        Dim conn As New SqlConnection(), conn2 As New SqlConnection(), newTableLayout As TableLayout, newDatabase As DatabaseStructure, dbServer As New DatabaseServer
        Dim reader As SqlDataReader, dcmd As SqlCommand

        'dbServer = frmMainApp.tvwDatabaseLO.Tag
        dbServer.Name = "Odin"
        dbServer.ConnectionString = "Data Source=localhost;Initial Catalog=Corp;Persist Security Info=True;User ID=sa;Password=`1QW~!qw"

        'create a database 
        newDatabase = New DatabaseStructure
        newDatabase.Name = "Corp"
        newDatabase.ConnectionString = "Data Source=localhost;Initial Catalog=Corp;Persist Security Info=True;User ID=sa;Password=`1QW~!qw"

        conn.ConnectionString = newDatabase.ConnectionString
        conn.Open()

        conn2.ConnectionString = newDatabase.ConnectionString
        conn2.Open()

        dcmd = New SqlCommand("select table_name from INFORMATION_SCHEMA.TABLES where table_type='BASE TABLE'", conn2)

        reader = dcmd.ExecuteReader

        Do While reader.Read
            newTableLayout = New TableLayout
            newTableLayout = PopulateTable(reader("TABLE_NAME"), newTableLayout) '"tblStoreMaster")'conn, 
            newDatabase.TableLayouts.Add(reader("TABLE_NAME").ToString, newTableLayout)
        Loop

        dbServer.Databases.Add(newDatabase.Name, newDatabase)

        CreateDBLayoutTreeView(frmMainApp.tvwDatabaseLO, dbServer)

        conn.Close()
        conn2.Close()

        'strTable = "tblStoreMaster"
        pageObjects(1) = dbServer

    End Sub


    Function LoadSerializedFilex(ByVal strFileName As String, ByVal ObjectType As String) As Object

        Dim f As New BinaryFormatter
        Dim s As FileStream
        'Dim mytype As Type = Type.GetType("LayoutTemplate")

        Dim layoutTemplate As New Object

        'open file
        Try
            s = New FileStream(strFileName, FileMode.Open)
        Catch ex As Exception
            LoadSerializedFilex = Nothing
            Exit Function

        End Try

        If s.Length = 0 Then
            LoadSerializedFilex = Nothing
        Else
            LoadSerializedFilex = Convert.ChangeType(f.Deserialize(s), Type.GetType(ObjectType)) 'CType(f.Deserialize(s), LayoutTemplate) '
        End If
        s.Close()

    End Function


    Sub LoadTemplateLayouTree(ByVal strFileName As String)

        Dim f As New BinaryFormatter
        Dim s As FileStream

        Dim layoutTemplate As New LayoutTemplate

        'open file
        s = New FileStream(strFileName, FileMode.Open)


        If s.Length = 0 Then
            layoutTemplate.LayoutName = "new layout"
        Else
            'layoutTemplate = CType(f.Deserialize(s), LayoutTemplate)
            layoutTemplate = Convert.ChangeType(f.Deserialize(s), Type.GetType("dban.LayoutTemplate"))
            layoutTemplate.FileName = strFileName

        End If


        s.Close()
        CreateTreeView(frmMainApp.TreeView1, layoutTemplate)

    End Sub


End Module
